package org.hgvc.webapp.ets.spring.dao;

import java.util.List;

import org.hgvc.webapp.ets.bo.Agreement;
import org.hgvc.webapp.ets.bo.AmortiseSchema;
import org.hgvc.webapp.ets.bo.ComplimentaryBooking;
import org.hgvc.webapp.ets.bo.CreditTerm;
import org.hgvc.webapp.ets.bo.Membership;
import org.hgvc.webapp.ets.bo.MembershipCategory;
import org.hgvc.webapp.ets.bo.PointCategory;
import org.hgvc.webapp.ets.bo.PointMembershipMap;
import org.hgvc.webapp.ets.bo.ReInstateFreeTrx;
import org.hgvc.webapp.ets.constant.CommonConstant;
import org.hibernate.Criteria;
import org.hibernate.Query;
import org.hibernate.criterion.Restrictions;
import org.jocsb.base.AbstractHibernateDao;


public class MembershipHbmDao extends AbstractHibernateDao {

	public List<PointCategory> getPointCategoryList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select point from PointCategory point where point.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<PointCategory>)query.list();
	}

	public List<PointCategory> getPointCategoryListWithNameCode(String name,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select point from PointCategory point where point.isDeleted=:value ");
		str.append(" and (point.code=:code or point.name=:name) ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<PointCategory>)query.list();
	}
	
	public List<AmortiseSchema> getAmortiseSchemaList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select schema from AmortiseSchema schema where schema.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<AmortiseSchema>)query.list();
	}
	
	
	public List<AmortiseSchema> getAmortiseSchemaListWithNameCode(String name,String code)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select schema from AmortiseSchema schema where schema.isDeleted=:value ");
		str.append(" and (schema.code=:code or schema.name=:name) ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<AmortiseSchema>)query.list();
	}
	
	public List<CreditTerm> getCreditTermList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select term from CreditTerm term where term.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<CreditTerm>)query.list();
	}
	
	
	public List<CreditTerm> getCreditTermListWithCodeName(String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select term from CreditTerm term where term.isDeleted=:value ");
		str.append(" and (term.code=:code or term.name=:name) ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<CreditTerm>)query.list();
	}
	
	public List<MembershipCategory> getMembershipCategoryList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from MembershipCategory cat where cat.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<MembershipCategory>)query.list();
	}
	
	public List<MembershipCategory> getMembershipCategoryListWithCodeName(String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select cat from MembershipCategory cat where cat.isDeleted=:value ");
		str.append(" and (cat.code=:code or cat.name=:name) ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<MembershipCategory>)query.list();
	}
	
	public List<Membership> getMembershipList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select membership from Membership membership where membership.isDeleted=:value ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<Membership>)query.list();
	}
	
	public List<Membership> getMembershipListByCatId(String catId)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select membership from Membership membership where membership.isDeleted=:value ");
		str.append(" and  membership.membershipCategory.membershipCatId=:catId");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("catId", catId);
		return (List<Membership>)query.list();
	}
	
	public List<Membership> getMembershipListByCatIdCodeName(String catId,String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select membership from Membership membership where membership.isDeleted=:value ");
		str.append(" and  membership.membershipCategory.membershipCatId=:catId");
		str.append(" and (membership.code=:code or membership.name=:name) ");
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("catId", catId);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<Membership>)query.list();
	}
	
	public List<ReInstateFreeTrx> getReInstateFreeTrxList()throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select res from ReInstateFreeTrx res where res.isDeleted=:value ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		return (List<ReInstateFreeTrx>)query.list();
	}
	
	
	public List<ReInstateFreeTrx> getReInstateFreeTrxListWithCodeName(String code,String name)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select res from ReInstateFreeTrx res where res.isDeleted=:value ");
		str.append(" and (res.code=:code or res.name=:name) ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("name", name);
		query.setParameter("code", code);
		return (List<ReInstateFreeTrx>)query.list();
	}
	
	public List<PointMembershipMap> getPointMembershipMapList(String id)throws Exception{
		StringBuffer str = new StringBuffer();
		str.append(" select p from PointMembershipMap p where p.isDeleted=:value ");
		str.append(" and p.membership.membershipId=:id ");
		
		Query query = getSession().createQuery(str.toString());
		query.setParameter("value", CommonConstant.NOT_DELETED);
		query.setParameter("id", id);
		return (List<PointMembershipMap>)query.list();
	}
}
